﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Drawing.Printing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Media;
using System.IO;
using System.Windows.Threading;
using System.Data.OleDb;
using System.Threading;
using System.Diagnostics;
using System.Text.RegularExpressions;
using Log;

namespace PayManage
{
    public partial class frmMain : Form
    {
        //private int x;
        //private int y;
        private bool hidden;
        //private Point myPoint;
        private static readonly ILog logger = LogManager.GetLogger(typeof(frmMain));
        private string currentSheet;
        private string currentFile;
        public delegate void InitTreeViewHander(object message);
        private event InitTreeViewHander OnInitTreeView;

        private event InitTreeViewHander OnAlert;

        public delegate void InitAfterSelectHander();
        private event InitAfterSelectHander OnInitAfterSelect;
        private event InitAfterSelectHander OnLoadData;

        private static bool CanOperate;
        private static string CurrentOperateMsg;
        Dispatcher dispatcher = Dispatcher.CurrentDispatcher;
        public frmMain()
        {
            InitializeComponent();
            //DataService.AnimateWindow(this.Handle, 1000, DataService.AW_SLIDE | DataService.AW_ACTIVATE | DataService.AW_CENTER);          

            //打开对话框
            this.openFileDialog1.Filter = "Excel2003表格(*.xls)|*.xls|Excel2007表格(*.xlsx)|*.xlsx";
            this.openFileDialog1.Title = "选择工资表格";
            this.openFileDialog1.FileName = "";
            //this.openFileDialog1.InitialDirectory = "c:\\";

            //保存对话框
            this.saveFileDialog1.Filter = "Excel2003表格(*.xls)|*.xls|Excel2007表格(*.xlsx)|*.xlsx";
            this.saveFileDialog1.Title = "另存工资表";
            this.saveFileDialog1.FileName = "";
            //this.saveFileDialog1.InitialDirectory = "c:\\";

        }

        private void test()
        {

        }
        /// <summary>
        /// 初始化TreeView节点
        /// </summary>
        private void InitTreeView(object message)
        {
            if (this.treeView1.InvokeRequired)
            {
                this.treeView1.Invoke(OnInitTreeView, new object { });
            }
            else
            {
                this.treeView1.ImageList = this.imageList1;
                this.treeView1.SelectedImageIndex = 1;
                this.treeView1.Nodes.Clear();
                //加载文件
                foreach (var file in DataService.SysConfigs.ExcelFiles)
                {
                    if (File.Exists(file))
                    {
                        InitTreeFromFile(file);
                    }
                }
                //Thread th = new Thread(new ParameterizedThreadStart(InitTreeComplate));
                //th.Start("加载树完毕");
                //ShowWelcome("加载表格成功", 500, 6);

            }
        }

        private string WelcomeString;
        private static int count;
        private static int MaxCount;
        System.Windows.Forms.Timer mytime = new System.Windows.Forms.Timer();
        /// <summary>
        /// 定时器
        /// </summary>
        /// <param name="message"></param>
        /// <param name="interval"></param>
        /// <param name="maxcount">震动次数</param>
        private void ShowWelcome(string message, int interval, int maxcount)
        {
            count = 0;
            MaxCount = maxcount;
            mytime.Interval = interval;
            WelcomeString = this.WelcomeLabel.Text;
            this.WelcomeLabel.Text = message;
            mytime.Tick += new EventHandler(mytime_Tick);
            mytime.Start();
        }
        void mytime_Tick(object sender, EventArgs e)
        {

            if (count <= MaxCount && count > -1)
            {

                int div = count % 3;
                switch (div)
                {
                    case 0:
                        this.WelcomeLabel.ForeColor = Color.Red;
                        break;
                    case 1:
                        this.WelcomeLabel.ForeColor = Color.Blue;
                        break;
                    case 2:
                        this.WelcomeLabel.ForeColor = Color.Yellow;
                        break;
                    default:
                        break;
                }
                count++;
            }
            else
            {
                this.WelcomeLabel.Text = WelcomeString;
                this.WelcomeLabel.ForeColor = Color.Black;
                mytime.Stop();
                count = -1;
            }



        }
        private void InitTreeFromFile(string file)
        {
            try
            {
                var fileName = Path.GetFileNameWithoutExtension(file);
                //打开文件并加载成功
                List<string> sheetList = null;
                try
                {
                    DataService.OpenExcelFile(file, out sheetList);
                    //logger.Debug(string.Format("打开文件{0}并加载成功", file));
                }
                catch (Exception ex)
                {
                    logger.Error(ex.Message);
                }

                TreeNode fileNode = new TreeNode(fileName);
                fileNode.StateImageIndex = 1;
                fileNode.SelectedImageIndex = 2;
                fileNode.Name = file;
                currentFile = file;
                foreach (var sheet in sheetList)
                {
                    fileNode.Nodes.Add(new TreeNode { StateImageIndex = 1, SelectedImageIndex = 2, Name = sheet, Text = sheet });

                }

                this.treeView1.Nodes.Add(fileNode);

            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                DataService.ShowErrorMsg(ex.Message);
            }
        }
        private void 打开表格OToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (this.openFileDialog1.ShowDialog().Equals(DialogResult.OK))
            {
                string file = this.openFileDialog1.FileName;
                if (!this.treeView1.Nodes.ContainsKey(file))
                {
                    InitTreeFromFile(file);
                    actionState.Text = "当前操作：" + file;
                }
            }
        }


        //双击notifyIcon图标显示窗体
        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (hidden)
            {
                formShow();
                this.Activate();
            }
            else
            {
                formHide();
            }
        }

        private void formHide()
        {
            if (!this.WindowState.Equals(FormWindowState.Minimized))
                this.WindowState = FormWindowState.Minimized;
            hidden = true;
            notifyIcon1.ShowBalloonTip(1000, "提示！", "我就在这里，有事叫我哦！", ToolTipIcon.Info);

        }
        private void formShow()
        {

            if (this.WindowState.Equals(FormWindowState.Minimized))
                this.WindowState = FormWindowState.Normal;
            hidden = false;

        }

        private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.ExitThread();
            Application.Exit();
        }

        private void tool_DiamondGreen_Click(object sender, EventArgs e)
        {
            SetSkin("DiamondGreen");
        }

        private void tool_DiamondBlue_Click(object sender, EventArgs e)
        {
            SetSkin("DiamondBlue");
        }

        private void tool_GlassOrange_Click(object sender, EventArgs e)
        {
            SetSkin("GlassOrange");
        }

        private void tool_SteelBlue_Click(object sender, EventArgs e)
        {
            SetSkin("SteelBlue");
        }

        private void SetSkin(string skin)
        {
            this.skinEngine1.SkinFile = Application.StartupPath + "\\skins\\" + skin + ".ssk";
            DataService.SysConfigs.DefaultSkin = skin;
        }
        private void tool_DeepCyan_Click(object sender, EventArgs e)
        {
            SetSkin("DeepCyan");
        }

        private void tool_MacOS_Click(object sender, EventArgs e)
        {
            SetSkin("MacOS");
        }

        private void tool_MP10_Click(object sender, EventArgs e)
        {
            SetSkin("MP10");
        }

        private void tool_MSN_Click(object sender, EventArgs e)
        {
            SetSkin("GlassOrange");
        }

        private void tool_Exit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void tool_ShowForm_Click(object sender, EventArgs e)
        {
            if (hidden)
            {
                formShow();
            }
            this.Activate();
        }

        private void tool_HidenForm_Click(object sender, EventArgs e)
        {
            if (!hidden)
            {
                formHide();
            }

        }

        private void 修改密码ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            new frmChangePwd().ShowDialog();
        }

        private void frmMain_SizeChanged(object sender, EventArgs e)
        {
            if (this.WindowState.Equals(FormWindowState.Minimized))
            {
                hidden = true;
            }
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            try
            {
                try
                {

                    this.skinEngine1.SkinFile = Application.StartupPath + "\\skins\\" + DataService.SysConfigs.DefaultSkin + ".ssk";
                }
                catch
                {
                    DataService.SysConfigs.DefaultSkin = "MSN";
                    this.skinEngine1.SkinFile = Application.StartupPath + "\\skins\\MSN.ssk";
                }
                //状态栏
                WelcomeLabel.Text = "今天是农历" + DataService.getChineseDate();
                OnInitTreeView = new InitTreeViewHander(InitTreeView);
                OnAlert = new InitTreeViewHander(Alert);
                OnInitAfterSelect = new InitAfterSelectHander(InitAfterSelect);
                //OnLoadData = new InitAfterSelectHander(InitAfterSelect);
                Thread th = new Thread(new ParameterizedThreadStart(InitTreeView));
                th.Start(null);

                //打印机
                InitPrint();
                CanOperate = true;
                粘贴ToolStripMenuItem.Enabled = false;
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }

            //this.tool_hidenCol.it

        }

        private void 关于ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            new AboutSoftWare().ShowDialog();
        }

        private void 万年历KToolStripMenuItem_Click(object sender, EventArgs e)
        {
            new Calendar().Show();
        }

        private void 计算器CToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Process.Start(Environment.GetFolderPath(Environment.SpecialFolder.System) + "\\calc.exe");
        }

        private void 工具栏TToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (工具栏TToolStripMenuItem.Checked)
            {
                fillBy1ToolStrip.Visible = true;
            }
            else
            {
                fillBy1ToolStrip.Visible = false;
            }
        }

        private void 状态栏SToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (状态栏SToolStripMenuItem.Checked)
            {
                statusStrip1.Visible = true;
            }
            else
            {
                statusStrip1.Visible = false;
            }
        }

        private void 组别栏GToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (组别栏GToolStripMenuItem.Checked)
            {
                splitContainer1.Panel1Collapsed = false;//组别树展开
            }
            else
            {
                splitContainer1.Panel1Collapsed = true;//组别树折叠                
            }
        }

        private void toolStripButton7_Click(object sender, EventArgs e)
        {

        }

        private void nameToolStripLabel_Click(object sender, EventArgs e)
        {

        }

        private void nameToolStripTextBox_Click(object sender, EventArgs e)
        {

        }

        private void treeView1_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
        {

        }

        /// <summary>
        /// 双击节点，选择数据展示
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void treeView1_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
        {

        }

        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            Rectangle rectangle = new Rectangle(e.RowBounds.Location.X,
             e.RowBounds.Location.Y,
             dataGridView1.RowHeadersWidth - 4,
             e.RowBounds.Height);

            TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
                dataGridView1.RowHeadersDefaultCellStyle.Font,
                rectangle,
                dataGridView1.RowHeadersDefaultCellStyle.ForeColor,
                TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
        }

        private static DataTable currentDataSource;
        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {

            dispatcher.Invoke(OnInitAfterSelect);
            //InitAfterSelect();
        }

        private void InitAfterSelect()
        {
            if (treeView1.SelectedNode != null && treeView1.SelectedNode.Parent != null)
            {
                if (!string.IsNullOrEmpty(currentSheet) && (currentSheet != treeView1.SelectedNode.Name || currentFile != treeView1.SelectedNode.Parent.Name))
                {

                    if (MessageBox.Show("确认保存？" + currentSheet, "此保存不可恢复", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        if (DataService.SaveExcelFile(currentFile, currentSheet, this.dataGridView1))
                            Alert("保存表" + currentSheet + "成功");
                    }
                }
                currentFile = treeView1.SelectedNode.Parent.Name;
                actionState.Text = "当前操作：" + currentFile;


                //statusStrip1.Refresh();
                if (currentSheet != treeView1.SelectedNode.Name || currentFile != treeView1.SelectedNode.Parent.Name)
                {
                    var node = treeView1.SelectedNode;
                    //MessageBox.Show(string.Format("{0},{1},{2}",node.Text, node.Parent.Name,
                    //    node.FullPath));
                    this.toolStripStatusLabel2.Text = "当前分组：" + node.FullPath;
                    currentFile = node.Parent.Name;
                    currentSheet = node.Name;
                    ThDataBind();
                }
            }
            //在编辑菜单那生成批量设置工具

        }

        private void ThDataBind()
        {


            //根据节点名去查找cash
            currentDataSource = GetDataTableBySheetName(currentFile, currentSheet);

            this.dataGridView1.Columns.Clear();
            this.dataGridView1.DataSource = currentDataSource;
            DataGridViewColumnSelector cs = new DataGridViewColumnSelector(dataGridView1, menu_DataGridView);
            cs.MaxHeight = 100;
            cs.Width = 110;
            ((ToolStripMenuItem)menuStrip1.Items[1]).DropDownItems.Clear();
            ((ToolStripMenuItem)menuStrip1.Items[2]).DropDownItems.Clear();
            this.tool_hidenCol.DropDownItems.Clear();
            for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
            {
                var col = DataService.GetColumnById(currentSheet, this.dataGridView1.Columns[i].Name);

                //ToolStripMenuItem menu = new ToolStripMenuItem();
                //menu.Checked = !col.IsHidden;
                //menu.CheckOnClick = true;
                //menu.CheckState = col.IsHidden ? System.Windows.Forms.CheckState.Unchecked : System.Windows.Forms.CheckState.Checked;
                //menu.Name = col.Id;
                //menu.Text = col.HeadText;               
                //menu.Click += new EventHandler(menu_Click);
                //this.tool_hidenCol.DropDownItems.Add(menu);             
                this.dataGridView1.Columns[i].Frozen = col.IsFrozen;
                this.dataGridView1.Columns[i].Name = col.Id;
                this.dataGridView1.Columns[i].Visible = !col.IsHidden;
                this.dataGridView1.Columns[i].HeaderText = col.HeadText;
                this.dataGridView1.Columns[i].ToolTipText = col.HeadText;
                //只能公式计算
                if (col.MustMethod)
                {
                    string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => this.dataGridView1.Columns[match.Value.Trim('\'')].HeaderText.ToString());
                    this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：公式=" + method;
                    this.dataGridView1.Columns[i].ReadOnly = true;
                }

                //对于设置默认值的，从default里面取值，初始化
                if (col.CanSetDefault && !col.IsHidden)
                {
                    //                                
                    ToolStripMenuItem batchtool = new ToolStripMenuItem("默认设置" + col.HeadText);
                    batchtool.Name = "batch_tool_" + col.Id;
                    batchtool.Visible = true;
                    this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：可以批量设置默认值:" + col.DefaultValue;
                    batchtool.Click += new EventHandler(batchtool_Click);
                    ((ToolStripMenuItem)menuStrip1.Items[1]).DropDownItems.Add(batchtool);
                }
                //对于带公式的，也可以手动修改的
                if (col.SetOrMethod || col.IsWithDate || col.IsAllWorkAWard)
                {
                    //                           
                    if (col.SetOrMethod)
                    {
                        string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => this.dataGridView1.Columns[match.Value.Trim('\'')].HeaderText.ToString());
                        this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：手动或公式=" + method;
                    }
                    else if (col.IsWithDate)
                    {
                        this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：年资奖，月结日：" + DataService.ExcelSheetsConfigs.Month;
                    }
                    else
                    {
                        this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：全勤奖=" + col.DefaultValue + "，必须满足标准出勤等于实际出勤";
                    }
                    ToolStripMenuItem autotool = new ToolStripMenuItem("自动设置" + col.HeadText);
                    autotool.Name = "auto_tool_" + col.Id;
                    autotool.Visible = true;
                    autotool.Click += new EventHandler(autotool_Click);
                    ((ToolStripMenuItem)menuStrip1.Items[2]).DropDownItems.Add(autotool);
                }
            }
        }
        private void DataBind()
        {


            //根据节点名去查找cash
            currentDataSource = GetDataTableBySheetName(currentFile, currentSheet);

            this.dataGridView1.Columns.Clear();
            this.dataGridView1.DataSource = currentDataSource;
            DataGridViewColumnSelector cs = new DataGridViewColumnSelector(dataGridView1, menu_DataGridView);
            cs.MaxHeight = 100;
            cs.Width = 110;
            ((ToolStripMenuItem)menuStrip1.Items[1]).DropDownItems.Clear();
            ((ToolStripMenuItem)menuStrip1.Items[2]).DropDownItems.Clear();
            this.tool_hidenCol.DropDownItems.Clear();
            for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
            {
                var col = DataService.GetColumnById(currentSheet, this.dataGridView1.Columns[i].Name);

                //ToolStripMenuItem menu = new ToolStripMenuItem();
                //menu.Checked = !col.IsHidden;
                //menu.CheckOnClick = true;
                //menu.CheckState = col.IsHidden ? System.Windows.Forms.CheckState.Unchecked : System.Windows.Forms.CheckState.Checked;
                //menu.Name = col.Id;
                //menu.Text = col.HeadText;               
                //menu.Click += new EventHandler(menu_Click);
                //this.tool_hidenCol.DropDownItems.Add(menu);             
                this.dataGridView1.Columns[i].Frozen = col.IsFrozen;
                this.dataGridView1.Columns[i].Name = col.Id;
                this.dataGridView1.Columns[i].Visible = !col.IsHidden;
                this.dataGridView1.Columns[i].HeaderText = col.HeadText;
                this.dataGridView1.Columns[i].ToolTipText = col.HeadText;
                //只能公式计算
                if (col.MustMethod)
                {
                    string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => this.dataGridView1.Columns[match.Value.Trim('\'')].HeaderText.ToString());
                    this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：公式=" + method;
                    this.dataGridView1.Columns[i].ReadOnly = true;
                }

                //对于设置默认值的，从default里面取值，初始化
                if (col.CanSetDefault && !col.IsHidden)
                {
                    //                                
                    ToolStripMenuItem batchtool = new ToolStripMenuItem("默认设置" + col.HeadText);
                    batchtool.Name = "batch_tool_" + col.Id;
                    batchtool.Visible = true;
                    this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：可以批量设置默认值:" + col.DefaultValue;
                    batchtool.Click += new EventHandler(batchtool_Click);
                    ((ToolStripMenuItem)menuStrip1.Items[1]).DropDownItems.Add(batchtool);
                }
                //对于带公式的，也可以手动修改的
                if (col.SetOrMethod || col.IsWithDate || col.IsAllWorkAWard)
                {
                    //                           
                    if (col.SetOrMethod)
                    {
                        string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => this.dataGridView1.Columns[match.Value.Trim('\'')].HeaderText.ToString());
                        this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：手动或公式=" + method;
                    }
                    else if (col.IsWithDate)
                    {
                        this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：年资奖，月结日：" + DataService.ExcelSheetsConfigs.Month;
                    }
                    else
                    {
                        this.dataGridView1.Columns[i].ToolTipText = col.HeadText + "：全勤奖=" + col.DefaultValue + "，必须满足标准出勤等于实际出勤";
                    }
                    ToolStripMenuItem autotool = new ToolStripMenuItem("自动设置" + col.HeadText);
                    autotool.Name = "auto_tool_" + col.Id;
                    autotool.Visible = true;
                    autotool.Click += new EventHandler(autotool_Click);
                    ((ToolStripMenuItem)menuStrip1.Items[2]).DropDownItems.Add(autotool);
                }
            }
        }

        ToolStripMenuItem my_menu;
        void menu_Click(object sender, EventArgs e)
        {
            my_menu = sender as ToolStripMenuItem;
            this.dataGridView1.Columns[my_menu.Name].Visible = my_menu.Checked;

        }
        private void ShowCurrentOperate()
        {
        }
        /// <summary>
        /// 默认值
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void batchtool_Click(object sender, EventArgs e)
        {
            try
            {


                ToolStripMenuItem batchtool = (ToolStripMenuItem)sender;
                var col = DataService.GetColumnById(currentSheet, batchtool.Name.Replace("batch_tool_", ""));
                if (col != null)
                {
                    //BatchSetValue frm = new BatchSetValue(((System.Xml.XmlNode[])col.DefaultValue)[0].InnerText);
                    BatchSetValue frm = new BatchSetValue(col.HeadText, col.DefaultValue);
                    if (frm.ShowDialog() == DialogResult.OK)
                    {
                        for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                        {
                            this.dataGridView1.Rows[i].Cells[col.Id].Value = frm.DefaultValue;
                            //this.dataGridView1.Rows[i].Cells[col.Id].Style.BackColor = Color.Red;
                        }
                        //默认值改变之后自动计算
                        计算全部ToolStripMenuItem_Click(sender, e);
                    }

                }
                else
                {
                    throw new Exception("没有查询到列" + batchtool.Name.Replace("batch_tool_", ""));
                }
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }
        }

        /// <summary>
        /// 可以根据公式计算，也可以修改
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void autotool_Click(object sender, EventArgs e)
        {
            try
            {


                ToolStripMenuItem batchtool = (ToolStripMenuItem)sender;
                var col = DataService.GetColumnById(currentSheet, batchtool.Name.Replace("auto_tool_", ""));
                if (col != null)
                {
                    if (col.SetOrMethod)
                    {
                        for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                        {
                            string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[i].Cells[match.Value.Trim('\'')].Value.ToString()).ToString("f0"));
                            this.dataGridView1.Rows[i].Cells[col.Id].Value = cvt.ToDouble(strUtil.qswhEval1(method).ToString()).ToString("f0");
                            // 颜色变化
                            this.dataGridView1.Rows[i].Cells[col.Id].ToolTipText = method;
                            //this.dataGridView1.Rows[i].Cells[col.Id].Style.ForeColor = Color.Green;

                        }
                    }
                    else if (col.IsAllWorkAWard)
                    {
                        //'WorkDay'-'RealWorkDay'  ,如果相等，就取默认值
                        for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                        {
                            string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[i].Cells[match.Value.Trim('\'')].Value.ToString()).ToString());
                            if (strUtil.qswhEval1(method).ToString() == "0")
                                this.dataGridView1.Rows[i].Cells[col.Id].Value = col.DefaultValue;
                            else
                                this.dataGridView1.Rows[i].Cells[col.Id].Value = 0;
                            //颜色
                            this.dataGridView1.Rows[i].Cells[col.Id].ToolTipText = method;
                            this.dataGridView1.Rows[i].Cells[col.Id].Style.ForeColor = Color.Blue;

                        }

                    }
                    else if (col.IsWithDate)
                    {
                        // <CountMethod>EnterDate;15;50*'Year'</CountMethod>
                        string[] countMethod = col.CountMethod.Split(';');
                        if (countMethod.Length > 2)
                        {
                            for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                            {
                                DateTime enter = cvt.ToTime(this.dataGridView1.Rows[i].Cells[countMethod[0].Trim()].Value);
                                //上个月，可以配置
                                DateTime countdate = DateTime.Now.AddMonths(-1).AddDays(15 - DateTime.Now.Day);
                                if (!strUtil.IsNullOrEmpty(DataService.ExcelSheetsConfigs.Month))
                                {
                                    DateTime setCountMonth;
                                    DateTime.TryParse(DataService.ExcelSheetsConfigs.Month, out setCountMonth);
                                    if (setCountMonth != DateTime.MinValue)
                                        countdate = setCountMonth;
                                }
                                int year = countdate.Year - enter.Year;
                                if (year > 0)
                                {
                                    if (countdate.Month < enter.Month)
                                    {
                                        year -= 1;
                                    }
                                    else if (countdate.Month == enter.Month)
                                    {
                                        if (countdate.Day < enter.Day)
                                        {
                                            year -= 1;
                                        }
                                    }
                                }
                                string method = countMethod[2].Replace("'Year'", year.ToString()); ;//Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[i].Cells[match.Value.Trim('\'')].Value.ToString()).ToString());
                                this.dataGridView1.Rows[i].Cells[col.Id].Value = strUtil.qswhEval1(method);
                                this.dataGridView1.Rows[i].Cells[col.Id].ToolTipText = method;
                                //this.dataGridView1.Rows[i].Cells[col.Id].Style.ForeColor = Color.Yellow;

                            }
                        }
                    }
                }
                else
                {
                    throw new Exception("没有查询到列" + batchtool.Name.Replace("batch_tool_", ""));
                }
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }
        }

        /// <summary>
        /// 只能公式计算
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void 计算全部ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //得到公式
            for (int j = 0; j < this.dataGridView1.Columns.Count; j++)
            {
                var colname = this.dataGridView1.Columns[j].Name;
                var col = DataService.GetColumnById(currentSheet, colname);
                //如果该列没有公式，那么下面的所有行都不需要改变
                if (!col.MustMethod && !col.SetOrMethod && !col.IsWithDate)
                    continue;
                if (col.MustMethod)
                {
                    for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                    {
                        string method = "";
                        try
                        {
                            method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[i].Cells[match.Value.Trim('\'')].Value.ToString()).ToString());
                            this.dataGridView1.Rows[i].Cells[j].Value = cvt.ToDouble(strUtil.qswhEval1(method).ToString()).ToString("f0");
                            this.dataGridView1.Rows[i].Cells[j].ToolTipText = method;
                            //this.dataGridView1.Rows[i].Cells[j].Style.BackColor = Color.Green;
                            this.dataGridView1.Rows[i].Cells[col.Id].Style.ForeColor = Color.Blue;
                        }
                        catch (Exception ex)
                        {
                            logger.Error(colname + "自动计算出错，" + method);
                        }

                    }
                }
                else if (col.SetOrMethod)
                {
                    for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                    {
                        string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[i].Cells[match.Value.Trim('\'')].Value.ToString()).ToString());
                        this.dataGridView1.Rows[i].Cells[col.Id].Value = cvt.ToDouble(strUtil.qswhEval1(method).ToString()).ToString("f0");
                        // 颜色变化
                        this.dataGridView1.Rows[i].Cells[col.Id].ToolTipText = method;
                        //this.dataGridView1.Rows[i].Cells[col.Id].Style.ForeColor = Color.Green;

                    }
                }
                else if (col.IsAllWorkAWard)
                {
                    //'WorkDay'-'RealWorkDay'  ,如果相等，就取默认值
                    for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                    {
                        string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[i].Cells[match.Value.Trim('\'')].Value.ToString()).ToString());
                        if (strUtil.qswhEval1(method).ToString() == "0")
                            this.dataGridView1.Rows[i].Cells[col.Id].Value = col.DefaultValue;
                        else
                            this.dataGridView1.Rows[i].Cells[col.Id].Value = 0;
                        //颜色
                        this.dataGridView1.Rows[i].Cells[col.Id].ToolTipText = method;
                        this.dataGridView1.Rows[i].Cells[col.Id].Style.ForeColor = Color.Blue;

                    }

                }



            }
        }

        private void Reflash()
        {
            //根据节点名去查找cash
            this.dataGridView1.DataSource = GetDataTableBySheetName(currentFile, currentSheet);
        }
        private DataTable GetDataTableBySheetName(string fileKey, string sheetName)
        {

            var fileCach = Caching.Cache[fileKey];
            if (fileCach == null || !((Dictionary<string, DataTable>)fileCach).Keys.Contains(currentSheet))
            {
                DataService.ReadExcelSheet(fileKey, sheetName);
            }
            fileCach = Caching.Cache[fileKey];
            return ((Dictionary<string, DataTable>)fileCach)[sheetName];
        }

        private void tool_Reflash_Click(object sender, EventArgs e)
        {
            //Reflash();
            //this.dataGridView1.Refresh();
        }

        private void 自动计算ToolStripMenuItem_Click(object sender, EventArgs e)
        {


        }

        /// <summary>
        /// 单元格内容变化了，如果影响其他格内容，应该在这里计算
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            //Alert("dataGridView1_CellValueChanged");
        }

        private void dataGridView1_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
        {
            //Alert("dataGridView1_CellValueNeeded");
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            try
            {
                if (!File.Exists(currentFile))
                {
                    DataService.ShowErrorMsg("请先选择一个可编辑Excel");
                    return;
                }
                if (MessageBox.Show("确认保存？" + currentSheet, "此保存不可恢复", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    //bool result = DataService.SaveExcelFile(currentFile, currentSheet, this.dataGridView1);
                    //if (result)
                    //{
                    //    Alert("保存文件成功");
                    //}
                    Thread th = new Thread(new ThreadStart(SaveFile));
                    th.Start();
                }
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }
        }

        private void SaveFile()
        {
            CanOperate = false;
            CurrentOperateMsg = "正在保存：" + currentFile + ":" + currentSheet;
            bool result = DataService.SaveExcelFile(currentFile, currentSheet, this.dataGridView1);
            CanOperate = true;
            if (result)
            {
                Alert("保存文件成功");
            }
        }
        private void menu_delete_Click(object sender, EventArgs e)
        {
            var select = this.treeView1.SelectedNode;
            if (select == null)
            {
                MessageBox.Show("请先选择一个Excel表");
                return;
            }
            if (DialogResult.Yes == MessageBox.Show("确定删除该分组？", "警告！", MessageBoxButtons.YesNo))
            {
                if (select.Parent == null)
                {
                    DataService.SysConfigs.ExcelFiles.Remove(select.Name);
                    DataService.Save();
                }
                else
                {
                }
                this.treeView1.Nodes.Remove(select);
            }
        }
        private static string key;
        private static int searchCount;
        /// <summary>
        /// 根据关键字查找
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Search_Click(object sender, EventArgs e)
        {
            if (key != this.nameToolStripTextBox.Text)
            {
                searchCount = 1;
            }
            else
            {
                searchCount++;

            }
            key = this.nameToolStripTextBox.Text;
            int forcount = 0;
            bool search = false;
            bool isHave = false;
            int firstIndex = -1;
            for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
            {

                if (this.dataGridView1.Rows[i].Cells["UserName"].Value.ToString().IndexOf(key) != -1)
                {
                    forcount++;
                    isHave = true;
                    if (firstIndex == -1)
                        firstIndex = i;
                    if (forcount == searchCount)
                    {
                        this.dataGridView1.CurrentCell = this.dataGridView1.Rows[i].Cells["UserName"];
                        search = true;
                        break;
                    }
                }
            }
            if (isHave && !search)
            {
                searchCount = 1;
                this.dataGridView1.CurrentCell = this.dataGridView1.Rows[firstIndex].Cells["UserName"];
            }
            else if (!isHave)
            {
                Alert("没有查询到");
            }

        }



        //打印文檔 
        PrintDocument pdDocument = new PrintDocument();

        //打印格式設置頁面 
        PageSetupDialog dlgPageSetup = new PageSetupDialog();

        //打印頁面 
        PrintDialog dlgPrint = new PrintDialog();

        //實例化打印預覽 
        PrintPreviewDialog dlgPrintPreview = new PrintPreviewDialog();

        private void InitPrint()
        {
            //预览
            this.toolStripButton3.Click += new EventHandler(toolStripButton3_Click);

            //打印
            toolStripButton2.Click += new EventHandler(toolStripButton2_Click);

            pdDocument.PrintPage += new PrintPageEventHandler(OnPrintPage);
            //頁面設置的打印文檔設置為需要打印的文檔 
            dlgPageSetup.Document = pdDocument;

            //打印界面的打印文檔設置為被打印文檔 
            dlgPrint.Document = pdDocument;

            //打印預覽的打印文檔設置為被打印文檔 
            dlgPrintPreview.Document = pdDocument;


        }
        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            //顯示打印預覽界面 
            dlgPrintPreview.ShowDialog();

        }
        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            pdDocument.Print();

        }

        ///  
        /// printDocument的PrintPage事件 
        ///  
        ///  
        ///  
        private void OnPrintPage(object sender, PrintPageEventArgs e)
        {
            int iX = 10;
            int iY = 10;
            PrintDataGridView.Print(this.dataGridView1, true, e, ref iX, ref iY);
        }
        ///  
        /// EndPrint事件釋放BeginPrint方法中佔用的資源 
        ///  
        ///  
        ///  
        void pdDocument_EndPrint(object sender, PrintEventArgs e)
        {

        }
        ///  
        /// DataGridView转换为二维数组 
        ///  
        ///  
        ///  
        ///  
        private string[,] ToStringArray(DataGridView dataGridView, bool includeColumnText)
        {

            string[,] arrReturn = null;
            int rowsCount = dataGridView.Rows.Count;
            int colsCount = dataGridView.Columns.Count;
            if (rowsCount > 0)
            {
                //最后一行是供输入的行时，不用读数据。 
                if (dataGridView.Rows[rowsCount - 1].IsNewRow)
                {
                    rowsCount--;
                }
            }
            int i = 0;
            //包括列标题 
            if (includeColumnText)
            {
                rowsCount++;
                arrReturn = new string[rowsCount, colsCount];
                for (i = 0; i < colsCount; i++)
                {
                    arrReturn[0, i] = dataGridView.Columns[i].HeaderText;
                }
                i = 1;
            }
            else
            {
                arrReturn = new string[rowsCount, colsCount];
            }
            //读取单元格数据 
            int rowIndex = 0;
            for (; i < rowsCount; i++, rowIndex++)
            {
                for (int j = 0; j < colsCount; j++)
                {
                    arrReturn[i, j] = dataGridView.Rows[rowIndex].Cells[j].Value.ToString();
                }
            }
            return arrReturn;
        }

        private void toolStripButton3_Click_1(object sender, EventArgs e)
        {

        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                var rows = this.dataGridView1.SelectedRows;
                if (this.dataGridView1.SelectedRows == null)
                {
                    Alert("对不起，选择要删除一行！");
                    return;
                }
                string message = "您确实要删除选定记录吗？";
                string caption = "删除提醒";
                for (int i = 0; i < rows.Count; i++)
                {
                    message += rows[i].Cells["UserName"].Value + "\t";
                }
                MessageBoxButtons buttons = MessageBoxButtons.YesNo;
                DialogResult result;
                result = MessageBox.Show(this, message, caption, buttons, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign);
                if (result == DialogResult.Yes)
                {
                    for (int i = 0; i < rows.Count; i++)
                    {
                        this.dataGridView1.Rows.Remove(rows[i]);
                    }
                    for (int i = 0; i < currentDataSource.Columns.Count; i++)
                    {
                        var col = DataService.GetColumnById(currentSheet, currentDataSource.Columns[i].ColumnName);
                        if (col != null && col.IsAutoNum)
                        {
                            for (int j = 0; j < currentDataSource.Rows.Count; j++)
                            {
                                currentDataSource.Rows[j][col.Id] = j + 1;
                            }
                            break;
                        }
                    }
                    this.dataGridView1.DataSource = currentDataSource;

                }
            }
            catch (Exception ex)
            {
                Alert(ex.Message);
            }


        }
        private void Alert(object message)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(OnAlert, message);
            }
            else
            {
                MessageBox.Show(message.ToString(), "操作提示");
            }
        }

        private void tool_data_Insert_Click(object sender, EventArgs e)
        {
            try
            {
                DataRow dr = currentDataSource.NewRow();

                var rows = this.dataGridView1.SelectedRows;
                int newRowIndex;
                if (rows == null)
                {
                    currentDataSource.Rows.Add(dr);
                }
                else
                {
                    //this.dataGridView1.Rows
                    //在选中位置新增一行，
                    currentDataSource.Rows.InsertAt(dr, this.dataGridView1.Rows.IndexOf(rows[0]));
                    //this.dataGridView1.Rows.Insert(this.dataGridView1.Rows.IndexOf(rows[0]));//异常，不能编程方式
                }
                for (int i = 0; i < currentDataSource.Columns.Count; i++)
                {
                    var col = DataService.GetColumnById(currentSheet, currentDataSource.Columns[i].ColumnName);
                    if (col != null && col.IsAutoNum)
                    {
                        for (int j = 0; j < currentDataSource.Rows.Count; j++)
                        {
                            currentDataSource.Rows[j][col.Id] = j + 1;
                        }
                        break;
                    }
                }
                this.dataGridView1.DataSource = currentDataSource;
                newRowIndex = currentDataSource.Rows.IndexOf(dr);
                this.dataGridView1.CurrentCell = this.dataGridView1.Rows[newRowIndex].Cells[0];

            }
            catch (Exception ex)
            {
                Alert(ex.Message);
            }

        }

        private void dataGridView1_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            //var col = DataService.GetColumnById(currentSheet, dataGridView1.Columns[e.ColumnIndex].Name);
            //if (String.IsNullOrEmpty(e.FormattedValue.ToString()))
            //{
            //    dataGridView1.Rows[e.RowIndex].ErrorText = col.HeadText+"不能为空";
            //    e.Cancel = true;
            //}

        }

        private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

            //if (e.Exception != null && e.Context == DataGridViewDataErrorContexts.Commit)
            //{
            //    DataService.ShowErrorMsg(e.Exception.Message);
            //}
            //DataService.ShowErrorMsg("输入" + dataGridView1.Columns[e.ColumnIndex].HeaderText + "数据错误");
            //e.Cancel = true;
            //if (this.dataGridView1.CurrentRow.IsNewRow)
            //{
            //    //athis.dataGridView1.CurrentRow.dele
            //}
        }

        private void 复制ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                Clipboard.SetDataObject(dataGridView1.GetClipboardContent());
                this.粘贴ToolStripMenuItem.Enabled = true;
            }
            catch
            {

            }


        }

        private void 粘贴ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            /*
            if (dataGridView1.CurrentCell == null)
                return;
            int insertRowIndex = dataGridView1.CurrentCell.RowIndex;//获取剪切板的内容，并按行分割 
            string pasteText = Clipboard.GetText();
            if (string.IsNullOrEmpty(pasteText))
               return ;
         
                //按 Tab 分割数据 
                string[] vals = pasteText.Split('\t');
                //判断列数是否统一 
                if (vals.Length - 1 < dataGridView1.ColumnCount)
                    throw new ApplicationException("粘贴的列数不正确。");
                DataGridViewRow row = dataGridView1.Rows[insertRowIndex];
                // 行头设定 
                // row.HeaderCell.Value = vals[0];
                //单元格内容设定 
                for (int i = 0; i < row.Cells.Count; i++)
                {
                    row.Cells[i].Value = vals[i-1];
                }                */
            try
            {
                PasteRow();
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }


        }

        private void PasteRow()
        {
            //当前单元格是否选择的判断
            if (dataGridView1.CurrentCell == null)
                return;
            int insertRowIndex = dataGridView1.CurrentCell.RowIndex;

            // 获取剪切板的内容，并按行分割
            string pasteText = Clipboard.GetText();
            if (string.IsNullOrEmpty(pasteText))
                return;
            pasteText = pasteText.Replace(" ", " ");
            pasteText = pasteText.Replace(' ', ' ');
            pasteText.TrimEnd(new char[] { ' ' });
            string[] lines = pasteText.Split(' ');

            //bool isHeader = false;
            foreach (string line in lines)
            {
                // 按 Tab 分割数据
                string[] vals = line.TrimStart('\t').Split('\t');
                // 判断列数是否统一
                var showCols = DataService.GetColumnsEntityByName(currentSheet).Where((c) => c.IsHidden == false).ToList();
                if (vals.Length != showCols.Count)
                    continue;
                //throw new ApplicationException("粘贴的列数不正确。");          
                for (int i = 0; i < showCols.Count; i++)
                {
                    //var col = DataService.GetColumnById(currentSheet, dataGridView1.Columns[i].Name);
                    var col = showCols[i];
                    if (col == null)
                        continue;
                    if (col.IsAutoNum)
                        continue;
                    object cellvalue;
                    switch (col.type)
                    {
                        case "int":
                            cellvalue = cvt.ToInt(vals[i]);
                            break;
                        case "double":
                            cellvalue = cvt.ToDouble(vals[i]);
                            break;
                        case "datetime":
                            cellvalue = cvt.ToTime(vals[i]);
                            break;
                        default:
                            cellvalue = vals[i];
                            break;
                    }
                    currentDataSource.Rows[insertRowIndex][col.Id] = cellvalue;
                }
                //  DataGridView的行索引+1
                break;//支持一行复制
                //insertRowIndex++;
            }
            this.dataGridView1.DataSource = currentDataSource;
        }

        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            InitAfterSelectHander hander = new InitAfterSelectHander(SaveAsExcelFile);

            this.dispatcher.Invoke(hander);
        }

        private void SaveAsExcelFile()
        {
            try
            {
                if (this.saveFileDialog1.ShowDialog() == DialogResult.OK)
                {

                    string fileName = this.saveFileDialog1.FileName;

                    bool result = DataService.SaveAsExcelFile(currentFile, currentSheet, this.dataGridView1, fileName);
                    if (result)
                    {
                        this.statusStrip1.Text = "当前操作：" + fileName;
                        statusStrip1.Refresh();
                        DataService.SysConfigs.ExcelFiles.Remove(currentFile);
                        currentFile = fileName;
                        if (!DataService.SysConfigs.ExcelFiles.Contains(currentFile))
                            DataService.SysConfigs.ExcelFiles.Add(currentFile);
                        Alert("另存文件成功");
                        Thread th = new Thread(new ParameterizedThreadStart(InitTreeView));
                        th.Start(null);
                    }

                }
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }
        }
        public static bool IsNoteBook;
        private void 记事本NToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (!IsNoteBook)
            {
                FormJ frm = new FormJ();
                frm.Show();
                IsNoteBook = true;
            }
        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {

            if (currentEditCellValue.ToString() != this.dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString())
            {
                Dictionary<string, object> oldGridViewRow = new Dictionary<string, object>();
                for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
                {
                    oldGridViewRow.Add(this.dataGridView1.Columns[i].Name, this.dataGridView1.Rows[e.RowIndex].Cells[i].Value);
                }

                //Alert("changed");
                this.dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Style.ForeColor = Color.Red;
                var cols = DataService.GetColumnsEntityByName(currentSheet);
                if (cols != null)
                {
                    List<string> changedcol = new List<string>();
                    changedcol.Add(this.dataGridView1.Columns[e.ColumnIndex].Name);
                    foreach (var col in cols)
                    {
                        if (col.MustMethod)
                        {
                            foreach (var ccol in changedcol)
                            {
                                if (col.CountMethod.IndexOf("'" + ccol + "'") != -1)
                                {
                                    string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[e.RowIndex].Cells[match.Value.Trim('\'')].Value.ToString()).ToString());
                                    this.dataGridView1.Rows[e.RowIndex].Cells[col.Id].Value = cvt.ToDouble(strUtil.qswhEval1(method).ToString()).ToString("f0");
                                    //自动计算值变化，颜色框为绿
                                    this.dataGridView1.Rows[e.RowIndex].Cells[col.Id].ToolTipText = method;
                                    changedcol.Add(col.Id);
                                    break;
                                }
                            }

                        }
                        //防止手动计算的被冲抵掉
                        else if (col.SetOrMethod)
                        {
                            string method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(oldGridViewRow[match.Value.Trim('\'')].ToString()).ToString());
                            //取消比较 ，旧数据不对
                            //string oldmethodvalue = cvt.ToDouble(strUtil.qswhEval1(method).ToString()).ToString("f2");
                            //if (oldmethodvalue != cvt.ToDouble(oldGridViewRow[col.Id].ToString()).ToString("f2"))
                            //    continue;
                            foreach (var ccol in changedcol)
                            {
                                if (col.CountMethod.IndexOf("'" + ccol + "'") != -1)
                                {
                                    method = Regex.Replace(col.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(this.dataGridView1.Rows[e.RowIndex].Cells[match.Value.Trim('\'')].Value.ToString()).ToString());
                                    this.dataGridView1.Rows[e.RowIndex].Cells[col.Id].Value = cvt.ToDouble(strUtil.qswhEval1(method).ToString()).ToString("f0");
                                    //手动或公式的值变化，颜色框为黄色
                                    this.dataGridView1.Rows[e.RowIndex].Cells[col.Id].ToolTipText = method;
                                    changedcol.Add(col.Id);
                                    break;
                                }
                            }

                        }
                    }
                }
            }
        }
        object currentEditCellValue;
        private void dataGridView1_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            currentEditCellValue = this.dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
        }

        private void 设置月份ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            SetPayMonth frm = new SetPayMonth();
            frm.ShowDialog();
        }

        private void 表格配置TToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmSheetsManage frm = new frmSheetsManage();
            if (frm.ShowDialog() == DialogResult.OK)
            {
                //DataBind();
            }
        }

        private void excel密码PToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmExcelPwd frm = new frmExcelPwd();
            frm.ShowDialog();
        }
        private void menu_DataGridView_Closing(object sender, ToolStripDropDownClosingEventArgs e)
        {

        }

        private static DataTable AttendanceTable;
        private static IList<AttendanceConfigEntity> AttendanceList;
        /// <summary>
        /// 导入考勤
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripButton8_Click(object sender, EventArgs e)
        {
            MessageBoxButtons buttons = MessageBoxButtons.YesNo;
            DialogResult result;
            result = MessageBox.Show(this, "确认导入考勤？导入过程有点慢，请稍候操作。", "提示", buttons, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign);
            if (result == DialogResult.Yes)
            {
                Dispatcher dispatcher = Dispatcher.CurrentDispatcher;
                Action openHander = new Action(OpenExcelByLine);
                dispatcher.Invoke(openHander);
            }
        }
        /// <summary>
        /// 导入考勤，缺点是时间太长，不好办
        /// </summary>
        private void OpenExcel()
        {
            try
            {
                //第一次，打开考勤表
                if (AttendanceTable == null || AttendanceTable.Rows.Count < 1 || AttendanceList == null || AttendanceList.Count < 1)
                {

                    this.openFileDialog1.Filter = "Excel2003表格(*.xls)|*.xls|Excel2007表格(*.xlsx)|*.xlsx";
                    this.openFileDialog1.Title = "选择表格";
                    this.openFileDialog1.FileName = "";
                    if (this.openFileDialog1.ShowDialog().Equals(DialogResult.OK))
                    {
                        string file = this.openFileDialog1.FileName;

                        //this.dataGridView1.DataSource = DataService.GetDataTableBySheetName(file, "");
                        AttendanceTable = DataService.GetTableBySheetName(file, "");
                        AttendanceList = new List<AttendanceConfigEntity>();
                        AttendanceList.Clear();
                        for (int i = 0; i < AttendanceTable.Rows.Count; i++)
                        {
                            DataRow dr = AttendanceTable.Rows[i];
                            if (cvt.ToInt(dr[3]) == 1 && cvt.ToInt(dr[4]) == 2 && cvt.ToInt(dr[5]) == 3)
                            {
                                AttendanceConfigEntity attend = new AttendanceConfigEntity();
                                attend.StartRowIndex = i;
                                attend.DeptName = cvt.ToNotNull(AttendanceTable.Rows[i - 1][1]);
                                AttendanceList.Add(attend);
                                if (AttendanceList.Count > 1)
                                {
                                    AttendanceList[AttendanceList.Count - 2].EndRowIndex = i - 1;
                                }
                            }
                        }
                        if (AttendanceList.Count > 1)
                        {
                            AttendanceList[AttendanceList.Count - 1].EndRowIndex = AttendanceTable.Rows.Count - 1;
                        }
                    }
                    else
                    {
                        Alert("你已经取消导入！");
                        return;
                    }
                }
                //如果存在考勤数据，则更新当前表格考勤数据
                if (AttendanceTable != null && AttendanceTable.Rows.Count > 0 && AttendanceList != null && AttendanceList.Count > 1)
                {
                    //根据当前表格去更新考勤
                    if (!string.IsNullOrEmpty(currentSheet))
                    {
                        string deptName = currentSheet;
                        currentDataSource = GetDataTableBySheetName(currentFile, currentSheet);
                        string userNames = "";//没有查询的员工名字
                        string selectUserNames = "";//查询到的员工名字
                        List<int> HasSelectRows = new List<int>();
                        List<int> GridSelectRows = new List<int>();
                        int NotFind = 0;
                        int HaveFind = 0;
                        bool ExistDept = false;
                        for (int j = 0; j < currentDataSource.Rows.Count; j++)
                        {
                            string userName = currentDataSource.Rows[j]["UserName"].ToString();
                            int Index = -1;
                            foreach (var attend in AttendanceList)
                            {
                                if (Index != -1)
                                    break;
                                if (deptName.IndexOf(attend.DeptName) != -1)
                                {
                                    ExistDept = true;
                                    for (int i = attend.StartRowIndex + 2; i < attend.EndRowIndex; i += 3)
                                    {
                                        //避免重复去比较，已经匹配了的，则忽略掉比较
                                        if (HasSelectRows.Contains(i))
                                            continue;
                                        if (cvt.ToNotNull(AttendanceTable.Rows[i][0]).Trim().Replace(" ", "") == userName)
                                        {
                                            Index = i;
                                            HasSelectRows.Add(i);
                                            break;
                                        }
                                    }

                                }

                            }
                            if (Index != -1)
                            {
                                //实际出勤
                                double realWorkDay = 0;
                                string memo = "";
                                //列查询
                                for (int i = 3; i < 34; i++)
                                {
                                    //上午出勤上午是3.5 下午是4.5  3.5/8小时=0.44，  4.5/8小时=0.56
                                    if (cvt.ToNotNull(AttendanceTable.Rows[Index][i]).Trim() == "√")
                                    {
                                        realWorkDay += 0.44;
                                    }
                                    if (cvt.ToNotNull(AttendanceTable.Rows[Index + 1][i]).Trim() == "√")
                                    {
                                        realWorkDay += 0.56;
                                    }
                                }

                                double otherAdd = 0;
                                double oneHourse = 0;
                                double oneFiveHourse = 0;
                                string[] overTime = new string[] { cvt.ToNotNull(AttendanceTable.Rows[Index + 1][35]).Trim(),
                    cvt.ToNotNull(AttendanceTable.Rows[Index + 2][35]).Trim(),
                     cvt.ToNotNull(AttendanceTable.Rows[Index + 3][35]).Trim()
                    };
                                foreach (var s in overTime)
                                {
                                    if (s.IndexOf("*4") != -1)
                                    {
                                        if (!s.StartsWith("*"))
                                            otherAdd = cvt.ToDouble(s.Substring(0, s.Length - s.IndexOf("*")));
                                    }
                                    if (s.IndexOf("*8") != -1)
                                    {
                                        if (!s.StartsWith("*"))
                                            oneHourse = cvt.ToDouble(s.Substring(0, s.Length - s.IndexOf("*")));
                                    }
                                    if (s.IndexOf("*10") != -1)
                                    {
                                        if (!s.StartsWith("*"))
                                            oneFiveHourse = cvt.ToDouble(s.Substring(0, s.Length - s.IndexOf("*")));
                                    }
                                }
                                if (currentDataSource.Columns.Contains("RealWorkDay"))
                                    currentDataSource.Rows[j]["RealWorkDay"] = realWorkDay;
                                if (currentDataSource.Columns.Contains("OneWorkHour"))
                                    currentDataSource.Rows[j]["OneWorkHour"] = oneHourse;
                                if (currentDataSource.Columns.Contains("OneFiveWorkHour"))
                                    currentDataSource.Rows[j]["OneFiveWorkHour"] = oneFiveHourse;
                                if (currentDataSource.Columns.Contains("OtherPayAdd"))
                                    currentDataSource.Rows[j]["OtherPayAdd"] = otherAdd;
                                if (currentDataSource.Columns.Contains("Memo"))
                                {
                                    memo = cvt.ToNotNull(AttendanceTable.Rows[Index + 1][34]).Trim();
                                    if (memo != "" && currentDataSource.Rows[j]["Memo"].ToString().IndexOf(memo) == -1)
                                    {
                                        currentDataSource.Rows[j]["Memo"] += memo;
                                    }
                                    memo = cvt.ToNotNull(AttendanceTable.Rows[Index + 2][34]).Trim();
                                    if (memo != "" && currentDataSource.Rows[j]["Memo"].ToString().IndexOf(memo) == -1)
                                    {
                                        currentDataSource.Rows[j]["Memo"] += memo;
                                    }
                                    memo += cvt.ToNotNull(AttendanceTable.Rows[Index + 3][34]).Trim();
                                    if (memo != "" && currentDataSource.Rows[j]["Memo"].ToString().IndexOf(memo) == -1)
                                    {
                                        currentDataSource.Rows[j]["Memo"] += memo;
                                    }
                                }
                                HaveFind += 1;
                                selectUserNames += userName + ",";                             
                                if (HaveFind % 6 == 0)
                                {
                                    selectUserNames += "\r\n";
                                }
                                GridSelectRows.Add(j);
                            }
                            else
                            {
                                userNames += userName + ",";
                                NotFind += 1;
                                if (NotFind % 6 == 0)
                                {
                                    userNames += "\r\n";
                                }
                            }
                        }
                        if (NotFind == 0 && HaveFind > 0)
                        {
                            Alert("全部人导入考勤数据，合计：" + HaveFind + "人导入考勤,,分别是\r\n" + selectUserNames);
                        }
                        else if (NotFind > 0 && HaveFind > 0)
                        {
                            Alert("合计：" + HaveFind + "人导入考勤," + NotFind + "人没有导入考勤,分别是\r\n" + userNames + "\r\n导入考勤人：" + selectUserNames);
                        }
                        else if (HaveFind == 0)
                        {
                            if (!ExistDept)
                            {
                                Alert("考勤表格中不存在" + currentSheet + "中的考勤部门，请核实！");
                            }
                            else
                            {
                                Alert("没有匹配任何人的考勤,合计：" + NotFind + "人没有导入考勤,分别是" + userNames);
                            }
                        }
                        if (HaveFind > 0)
                        {
                            this.dataGridView1.DataSource = currentDataSource;
                            for (int i = 0; i < GridSelectRows.Count; i++)
                            {
                                this.dataGridView1.Rows[GridSelectRows[i]].Cells["UserName"].Style.ForeColor = Color.Red;
                                this.dataGridView1.Rows[GridSelectRows[i]].Cells["UserName"].Style.BackColor = Color.Green;
                            }
                        }
                    }
                    else
                    {
                        Alert("请选择当前表格！");
                    }
                }
                else
                {
                    Alert("考勤表格数据不存在，或考勤表部门数据不存在，或……");
                }
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }

        }

        /// <summary>
        /// 导入考勤，优化查询效率
        /// </summary>
        private void OpenExcelByLine()
        {
            try
            {
               
                  this.openFileDialog1.Filter = "Excel2003表格(*.xls)|*.xls|Excel2007表格(*.xlsx)|*.xlsx";
                    this.openFileDialog1.Title = "选择表格";
                    this.openFileDialog1.FileName = "";
                    if (this.openFileDialog1.ShowDialog().Equals(DialogResult.OK))
                    {

                        string file = this.openFileDialog1.FileName;
                        string result = "";
                        System.Diagnostics.Stopwatch watch = new Stopwatch();
                        watch.Start();
                        DataService.GetAttendanceBySheetName(file, "", out result, this.dataGridView1, currentSheet);
                        watch.Stop();
                        Alert("当前导入耗时：" + watch.Elapsed.TotalSeconds + "秒!" + result);
                        //logger.Debug(result);
                    }
                    else
                    {
                        Alert("您取消了导入考勤");
                    }
               
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }

        }
    }
}
